package com.shudai.largescreen.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.shudai.largescreen.pojo.User;
import com.shudai.largescreen.pojo.dto.DeptDetailDto;
import com.shudai.largescreen.pojo.dto.UserDetailDto;
import com.shudai.largescreen.pojo.vo.Region;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.time.LocalDateTime;
import java.util.List;

@Repository
public interface PinCrownMapper extends BaseMapper<User> {

    /**
     * 得到所有地区变树
     *
     * @return
     */
    @Select({
            "SELECT sd.province label ,  sd.city as value  ,st.name as team",
            "FROM sd_tool_man_info sd ,sys_user su ,sys_dept st",
            "WHERE sd.user_id = su.id and su.dept_id = st.id and st.deleted_at is null and st.enabled = 1 ",
            "GROUP BY  sd.province ,sd.city ,st.name "
    })
    List<Region> findAllCityTeam();


    @Select({
            " SELECT u.nickname userName , sd.province province,sd.city city,sd.district district ,u.id id " +
                    " from sys_user u,sd_tool_man_info sd " +
                    " WHERE u.id = sd.user_id " +
                    " GROUP BY u.nickname,sd.province,sd.city,sd.district,u.id "
    })
    List<UserDetailDto> findUserDital();

    @Select({
            "<script>",
            "SELECT IFNULL(sum(o.amount),0) AS orderAmountWeek,",
            "o.tool_man_id as id ",

            "FROM sd_order o, sys_user su ",
            "WHERE o.tool_man_id=su.id  and o.order_state  in (2,3,12) and o.order_time >= #{parse} and o.on_cancel  = 0  ",
            " and o.tool_man_id in ",
            " <foreach collection=\"ids\" item=\"userId\" index=\"index\" open=\"(\" separator=\",\" close=\")\">",
            "#{userId}",
            "</foreach>",

            "GROUP BY o.tool_man_id ",
            "</script>"
    })
    List<UserDetailDto> findAllMoneyWeek(@Param("ids") List<Long> collect,
                                         @Param("parse") LocalDateTime localTime);

    @Select({
            "<script>",
            "SELECT IFNULL(count(sr.id),0) AS  customerWeek,",
            "u.id as id  ",
            "FROM sd_user_record sr, sys_user u  ",
            "WHERE sr.tool_man_id = u.id   and ( sr.deleted_at IS NULL and sr.created_at > #{parse}) ",
            " and sr.tool_man_id in ",
            " <foreach collection=\"ids\" item=\"userId\" index=\"index\" open=\"(\" separator=\",\" close=\")\">",
            "#{userId}",
            "</foreach>",

            "GROUP BY u.id ",
            "</script>"
    })
    List<UserDetailDto> findCustomerNew(@Param("ids") List<Long> collect,
                                        @Param("parse") LocalDateTime localTime);

    @Select({
            "<script>",
            "SELECT IFNULL(count(sr.id),0) AS customerAll,",
            "u.id as id  ",
            "FROM sd_user_record sr, sys_user u  ",
            "WHERE sr.tool_man_id = u.id   and ( sr.deleted_at IS NULL ) ",
            " and sr.tool_man_id in ",
            " <foreach collection=\"ids\" item=\"userId\" index=\"index\" open=\"(\" separator=\",\" close=\")\">",
            "#{userId}",
            "</foreach>",

            "GROUP BY u.id ",
            "</script>"
    })
    List<UserDetailDto> findAllCustomer(@Param("ids") List<Long> collect);

//sd.city as city , sd.district as district,sd.province province ,
//    sd.city , sd.district , ,sd.province
// sd_tool_man_info sd ,sys_user su ,
// "WHERE sd.user_id = su.id and su.dept_id = st.id and st.deleted_at is null and st.enabled = 1 ",
    @Select({
            "SELECT  st.name as deptName,st.id id",
            "FROM sys_dept st",
            "GROUP BY st.name,st.id "
    })
    List<DeptDetailDto> findDeptDetail();


    // ,sd.province ,sd.city,sd.district
    // ,sd.province province,sd.city city,sd.district  district
    @Select({
            "<script>",
            "SELECT IFNULL(sum(o.amount),0) AS orderAmountWeek,",
            "su.dept_id as id ",

            "FROM sd_order o, sys_user su ,sd_tool_man_info sd ",
            "WHERE o.tool_man_id=su.id  and o.order_state in (2,3,12) and o.order_time > #{parse} and sd.user_id = su.id and o.on_cancel  = 0  ",
            " and su.dept_id in ",
            " <foreach collection=\"ids\" item=\"deptId\" index=\"index\" open=\"(\" separator=\",\" close=\")\">",
            "#{deptId}",
            "</foreach>",

            "GROUP BY su.dept_id  ",
            "</script>"
    })
    List<DeptDetailDto> findAllMoneyWeekDept(@Param("ids") List<Long> collect,
                                             @Param("parse") LocalDateTime localTime);


    //,sd.province ,sd.city ,sd.district
    // ,sd.province province,sd.city city,sd.district  district
    @Select({
            "<script>",
            "SELECT IFNULL(count(sr.id),0) AS  customerWeek,",
            "u.dept_id as id   ",
            "FROM sd_user_record sr, sys_user u ,sd_tool_man_info sd ",
            "WHERE sr.tool_man_id = u.id   and ( sr.deleted_at IS NULL and sr.created_at > #{parse}) ",
            " and u.id = sd.user_id and u.dept_id in ",
            " <foreach collection=\"ids\" item=\"userId\" index=\"index\" open=\"(\" separator=\",\" close=\")\">",
            "#{userId}",
            "</foreach>",

            "GROUP BY u.dept_id    ",
            "</script>"
    })
    List<DeptDetailDto> findCustomerNewDept(@Param("ids") List<Long> collect,
                                            @Param("parse") LocalDateTime localTime);

    //,sd.province ,sd.city ,sd.district
    //,sd.province province,sd.city city,sd.district  district
    @Select({
            "<script>",
            "SELECT IFNULL(count(sr.id),0) AS  customerAll,",
            "u.dept_id as id   ",
            "FROM sd_user_record sr, sys_user u ,sd_tool_man_info sd ",
            "WHERE sr.tool_man_id = u.id   and ( sr.deleted_at IS NULL ) ",
            " and u.id = sd.user_id and u.dept_id in ",
            " <foreach collection=\"ids\" item=\"userId\" index=\"index\" open=\"(\" separator=\",\" close=\")\">",
            "#{userId}",
            "</foreach>",

            "GROUP BY u.dept_id    ",
            "</script>"
    })
    List<DeptDetailDto> findAllCustomerDept(@Param("ids") List<Long> collect);


    @Select({
            "SELECT o.user_record_id ,o.tool_man_id " +
                    "FROM sd_order o " +
                    "where o.order_state in (2,3,12) and o.on_cancel  = 0  and o.user_record_id is not null and o.tool_man_id is not null " +
                    " and o.order_time > #{parse} " +
                    "GROUP BY o.user_record_id ,o.tool_man_id "
    })
    List<DeptDetailDto> findAllUserRecordWeek(@Param("parse") LocalDateTime localTime);

    @Select({
            "<script>",
            "SELECT o.user_record_id userRecordId ,o.tool_man_id id " ,
            "FROM sd_order o WHERE " ,
            " o.user_record_id is not null and o.tool_man_id is not null and  o.order_state in(2,3,12) and o.on_cancel  = 0  " ,
            " and o.order_time BETWEEN #{startDate} and #{endDate} and o.tool_man_id in  ",
            "<foreach collection=\"ids\" item=\"ids\" index=\"index\" open=\"(\" separator=\",\" close=\")\">",
            "#{ids}",
            "</foreach>",
            "GROUP BY o.user_record_id ,o.tool_man_id ",
            "</script>"
    })
    List<UserDetailDto> findOrderWeek(@Param("ids") List<Long> collect,
                                      @Param("startDate") LocalDateTime startDate,
                                      @Param("endDate") LocalDateTime endDate
                                      );


    @Select({
            "<script>",
            "SELECT count(o.id) total ,o.tool_man_id id ,o.user_record_id userRecordId " +
            "FROM sd_order o " +
            "where  o.order_time >   #{parse} and  o.user_record_id is not null and o.tool_man_id is not null  and  o.user_record_id  in " +
            " <foreach collection=\"ids\" item=\"ids\" index=\"index\" open=\"(\" separator=\",\" close=\")\">",
            "#{ids}",
            "</foreach>",
            "GROUP BY o.tool_man_id ,o.user_record_id ",
            "</script>"
    })
    List<UserDetailDto> findUserRate(@Param("ids") List<Long> collect,
                                     @Param("parse") LocalDateTime localTime);

    @Select({
            "<script>",
            "SELECT count(o.id) total ,o.tool_man_id id " +
              "FROM sd_order o " +
              "where o.order_time > #{parse} and  o.tool_man_id  in " +
             " <foreach collection=\"ids\" item=\"ids\" index=\"index\" open=\"(\" separator=\",\" close=\")\">",
            "#{ids}",
            "</foreach>",
            "GROUP BY o.tool_man_id ",
            "</script>"
    })
    List<UserDetailDto> findOrderWeekTatal(@Param("ids") List<Long> key,  @Param("parse")LocalDateTime localTime);


    @Select({
            "<script>",
            "  SELECT o.user_record_id userRecordId ,u.dept_id id " +
             " FROM sd_order o,sys_user u WHERE  " +
             " o.user_record_id is not null and o.tool_man_id is not null and  o.order_state in(2,3,12) and o.on_cancel  = 0 " +
             " and o.order_time BETWEEN #{startDate} and #{endDate} and u.id = o.tool_man_id and u.dept_id in  " +
             " <foreach collection=\"ids\" item=\"ids\" index=\"index\" open=\"(\" separator=\",\" close=\")\">",
            "#{ids}",
            "</foreach>",
             " GROUP BY o.user_record_id ,u.dept_id ",
            "</script>"
    })
    List<DeptDetailDto> findOrderWeekDept(@Param("ids") List<Long> collect,
                                          @Param("startDate") LocalDateTime startDate,
                                          @Param("endDate") LocalDateTime endDate);

    @Select({
            "<script>",
            "SELECT count(o.id) total ,u.dept_id id ,o.user_record_id userRecordId " +
             "FROM sd_order o,sys_user u " +
             "where o.order_state in(2,12,3) and o.on_cancel  = 0  and u.id = o.tool_man_id and o.order_time > #{parse} and  o.user_record_id  in " +
             " <foreach collection=\"ids\" item=\"ids\" index=\"index\" open=\"(\" separator=\",\" close=\")\">",
            "#{ids}",
            "</foreach>",
            "GROUP BY u.dept_id  ,o.user_record_id  ",
            "</script>"
    })
    List<DeptDetailDto> findUserRateDept(@Param("ids") List<Long> userRecordId,
                                         @Param("parse") LocalDateTime localTime);

    @Select({
            "<script>",
            "select count(o.id) total,u.dept_id id from sd_order o,sys_user u where o.on_cancel  = 0 ",
            " and u.id = o.tool_man_id  and o.order_time > #{parse} and u.dept_id in ",
            " <foreach collection=\"ids\" item=\"ids\" index=\"index\" open=\"(\" separator=\",\" close=\")\">",
            "#{ids}",
            "</foreach>",
            "GROUP BY u.dept_id  ",
            "</script>"
    })
    List<DeptDetailDto> findOrderWeekTatolDept(@Param("ids") List<Long> key,
                                               @Param("parse")LocalDateTime localTime);


    @Select({
            "<script>",
            "select u.nickname label ,u.id value  ",
            " from  sys_user u ,sd_tool_man_info s ",
            " where u.id = s.user_id and u.created_at >#{parse} and u.enabled = 1 ",
            "GROUP BY u.nickname ,u.id  ",
            "</script>"
    })
    List<Region> findSalesMan( @Param("parse")LocalDateTime localTime);

}
